<?php

// $Id$

require_once(TANGRA_MAIN_DIR.'interfaces/i_db_storable.class.php');
require_once(TANGRA_MAIN_DIR.'nls/date.inc.php');

require_once('tcms_vpage_category.class.php');
require_once($WSC->get_site_inc_dir().'modules/tangra_cms/classes/category/tcms_category_dbc.class.php');
require_once('tcms_vpage_dbc.class.php');


class TCMS_VPage_Category_DBC extends TCMS_VPage_Category implements I_DB_Storable {
	public function save(DB_Connection $dbc) {
		$ret = false;

		if ($this->get_id()) {
			$ret = $this->update($dbc);
		} else {
			$ret = $this->insert($dbc);
			$this->set_id($ret);
		}

		return $ret;
	}


	public function load_by_id(DB_Connection $dbc, $id) {
		tangra_if_not_int_throw_e($id);

		$ret = false;

		$sql = "select vpage, category, ordering from tcms_vpage_categories where id = $id";
		$rez = $dbc->execute($sql);

		if (!$rez->is_eof()) {
			$rez_obj = $rez->fetch_object();

			$this->set_id($id);
			$this->set_vpage($rez_obj->VPAGE);
			$this->set_category($rez_obj->CATEGORY);
			$this->set_ordering($rez_obj->ORDERING);

			$ret = $id;
		}

		return $ret;
	}


	protected function insert(DB_Connection $dbc) {
		$ret = false;

		$id = $dbc->generate_id('tcms_vpage_categories_seq');
		$vpage = $this->get_vpage();
		$category = $this->get_category();
		$ordering = $this->get_ordering();

		if ($id) {
			$sql = "insert into tcms_vpage_categories (id, ".
									      "vpage, ".
									      "category, ".
									      "ordering ".
									      ") ".
								"values ".
									      "($id,".
									      "$vpage, ".
									      "$category, ".
									      "$ordering ".
									      ")";
			$dbc->execute($sql);
			$ret = $id;
		} else {
			throw new TE_Exception('tcms_vpage_categories_seq');
		}

		return $ret;
	}


	protected function update(DB_Connection $dbc) {
		$id = $this->get_id();

		$vpage = $this->get_vpage();
		$category = $this->get_category();
		$ordering = $this->get_ordering();

		$sql = "update tcms_vpage_categories set ".
						"vpage = $vpage, ".
						"category = $category, ".
						"ordering = $ordering ".
					"where id = ".$id;

		$dbc->execute($sql);
		$ret = $id;

		return $ret;
	}


	public static function delete(DB_Connection $dbc, $id) {
		tangra_if_not_int_throw_e($id);

		$sql = "delete from tcms_vpage_categories where id = $id";
		$dbc->execute($sql);
	}


	public static function load_vpage_categories(DB_Connection $dbc, $vpage_id) {
		tangra_if_not_int_throw_e($vpage_id);

		$ret = array();
		$sql = "select category from tcms_vpage_categories where vpage = $vpage_id";

		$rez = $dbc->execute($sql);
		while($rez_obj = $rez->fetch_object()) {
			$tmp = new TCMS_Category_DBC();
			$tmp->load_by_id($dbc, $rez_obj->CATEGORY);

			$tmp_arr = array();
			TCMS_VPage_Category_DBC::load_trail($dbc, $rez_obj->CATEGORY, $tmp_arr);
			$ret[] = array_reverse($tmp_arr);
		}

		return $ret;
	}


	private static function load_trail(DB_Connection $dbc, $category_id, &$arr) {
		$tmp = new TCMS_Category_DBC();
		$tmp->load_by_id($dbc, $category_id);

		array_push($arr, array('id' => $tmp->get_id(), 'title' => $tmp->get_title()));
		if ($tmp->get_parent()) {
			TCMS_VPage_Category_DBC::load_trail($dbc, $tmp->get_parent(), $arr);
		}
	}


	public static function vpage_category_exists(DB_Connection $dbc, $vpage_id, $category_id) {
		tangra_if_not_int_throw_e($vpage_id);
		tangra_if_not_int_throw_e($category_id);

		$ret = false;

		$sql = "select id from tcms_vpage_categories where vpage = $vpage_id and category = $category_id";

		$rez = $dbc->execute($sql);
		if (!$rez->is_eod()) {
			$rez_obj = $rez->fetch_object();
			$ret = $rez_obj->ID;
		}

		return $ret;
	}


	public static function get_sql_for_cat_grid($category_id, $path, $online, $template, $sys_gen) {
		tangra_if_not_int_throw_e($category_id);

		$where = TCMS_VPage_Category_DBC::prepare_where_clause($path, $online, $template, $sys_gen);
		if ($where) {
			$where = " and ".$where;
		}

		$sql = "select p.id, ".
						"p.path, ".
						"t.name, ".
						"p.online, ".
						"p.sys_generated, ".
						"p.in_sitemap, ".
						"p.sitemap_weight, ".
						"t.id, ".
						"cat.ordering ".
					"from tcms_vpage_categories cat, ".
							"tcms_vpages p, ".
							"tcms_vpage_templates t ".
					"where cat.category = $category_id and cat.vpage = p.id and p.template = t.id".$where.
					' order by cat.ordering asc, p.id desc ';

		return $sql;
	}


	public static function get_sql_count_for_cat_grid($category_id, $path, $online, $template, $sys_gen) {
		tangra_if_not_int_throw_e($category_id);

		$where = TCMS_VPage_Category_DBC::prepare_where_clause($path, $online, $template, $sys_gen);
		if ($where) {
			$where = " and ".$where;
		}
		$sql = "select count(p.id) as total_rows from tcms_vpage_categories cat, tcms_vpages p where cat.category = $category_id and cat.vpage = p.id ".$where;

		return $sql;
	}




	public static function delete_for_vpage(DB_Connection $dbc, $vpage_id) {
		tangra_if_not_int_throw_e($vpage_id);

		$sql = "delete from tcms_vpage_categories where vpage = $vpage_id";
		$dbc->execute($sql);
	}


	public static function delete_for_category(DB_Connection $dbc, $category_id) {
		tangra_if_not_int_throw_e($category_id);

		$sql = "delete from tcms_vpage_categories where category = $category_id";
		$dbc->execute($sql);
	}


	public static function count_vpage_items_for_category(DB_Connection $dbc, $category_id) {
		tangra_if_not_int_throw_e($category_id);

		$sql = "select count(id) as C from tcms_vpage_categories where category = $category_id";
		$rez = $dbc->execute($sql);

		$rez_obj = $rez->fetch_object();

		return $rez_obj->C;
	}


	private static function prepare_where_clause($path = '', $online = false, $template = false, $sys_gen = false) {
		$path = TCMS_VPage_Category_DBC::remove_duplicate_asteriks($path);

		if ($path || $online || $template || $sys_gen) {
			$where = "";
			if ($path) {
				if (strpos($path, '*') !== false) {
					$path = addslashes($path);
					$path = str_replace('*', '%', $path);
					$where .= "p.path like '$path' ";
				} else {
					$path = addslashes($path);
					$where .= "p.path = '$path' ";
				}
			}

			switch($online) {
				case 1:
					$where .= ($where ? ' and ' : '')." online = 1";
					break;
				case 2:
					$where .= ($where ? ' and ' : '')." online = 0";
					break;
			}

			if ($template) {
				$where .= ($where ? ' and ' : '')." template = ".$template;
			}


			switch($sys_gen) {
				case 1:
					$where .= ($where ? ' and ' : '')." sys_generated = 1";
					break;
				case 2:
					$where .= ($where ? ' and ' : '')." sys_generated = 0";
					break;
			}
		} else {
			$where = '';
		}

		return $where;
	}


	private static function remove_duplicate_asteriks($filter) {
		$ret = $filter;
		while(strpos($ret, '**') !== false) {
			$ret = str_replace('**', '*', $ret);
		}

		return $ret;
	}


	public static function select_vpage_and_vpage_template_for_cat(DB_Connection $dbc, $category_id) {
		tangra_if_not_int_throw_e($category_id);

		$sql = "select vpages.id, vpages.path, vpt.wp_path, vpt.wp_class_name ".
						"from tcms_vpages vpages, ".
								"tcms_vpage_templates vpt, ".
								"tcms_vpage_categories vp_cats ".
						"where vp_cats.category = $category_id and ".
								"vp_cats.vpage = vpages.id and ".
								"vpages.online = 1 and ".
								"vpages.template = vpt.id ".
								"order by vp_cats.ordering asc";

		$rez = $dbc->execute($sql);
		$ret = array();
		while ($rez_obj = $rez->fetch_object()) {
			$ret[] = array('vpage' => $rez_obj->ID, 'path' => $rez_obj->PATH, 'wp_path' => $rez_obj->WP_PATH, 'wp_class_name' => $rez_obj->WP_CLASS_NAME);
		}

		return $ret;
	}


	public static function delete_for_vpage_and_category(DB_Connection $dbc, $vpage_id, $category_id) {
		tangra_if_not_int_throw_e($vpage_id);
		tangra_if_not_int_throw_e($category_id);

		$sql = "delete from tcms_vpage_categories where vpage = $vpage_id and category = $category_id";
		$dbc->execute($sql);
	}


	public static function move_to_top(DB_Connection $dbc, $vpage_id, $category_id) {
		tangra_if_not_int_throw_e($vpage_id);
		tangra_if_not_int_throw_e($category_id);

	}


	public static function reset_ordering(DB_Connection $dbc, $category_id) {
		tangra_if_not_int_throw_e($category_id);

		$sql = "update tcms_vpage_categories set ordering = 0 where category = $category_id";
		$dbc->execute($sql);
	}


	public static function recalc_ordering(DB_Connection $dbc, $category_id) {
		tangra_if_not_int_throw_e($category_id);

		$sql = "select id from tcms_vpage_categories where category = $category_id order by ordering asc";
		$rez = $dbc->execute($sql);
		$order = 10;
		while ($rez_obj = $rez->fetch_object()) {
			$sql = "update tcms_vpage_categories set ordering = $order where id = {$rez_obj->ID}";
			$dbc->execute($sql);
			$order += 10;
		}
	}


	public function load_by_vpage_and_category(DB_Connection $dbc, $vpage_id, $category_id) {
		tangra_if_not_int_throw_e($vpage_id);
		tangra_if_not_int_throw_e($category_id);

		$ret = false;

		$sql = "select id from tcms_vpage_categories where vpage = $vpage_id and category = $category_id";
		$rez = $dbc->execute($sql);
		if (!$rez->is_eod()) {
			$rez_obj = $rez->fetch_object();
			$ret = $this->load_by_id($dbc, $rez_obj->ID);
		}

		return $ret;
	}


	public static function move_up(DB_Connection $dbc, $vpage_id, $category_id) {
		tangra_if_not_int_throw_e($vpage_id);
		tangra_if_not_int_throw_e($category_id);

		$current = new TCMS_VPage_Category_DBC();

		if ($current->load_by_vpage_and_category($dbc, $vpage_id, $category_id)) {
			$sql = "select id from tcms_vpage_categories where "
						."category = $category_id "
						." and ordering < ".$current->get_ordering()
						." order by ordering desc";

			$rez = $dbc->execute($sql);
			if (!$rez->is_eod()) {
				$above = new TCMS_VPage_Category_DBC();
				$rez_obj = $rez->fetch_object();
				$above->load_by_id($dbc, $rez_obj->ID);

				// Swaping orders
				$tmp = $current->get_ordering();
				$current->set_ordering($above->get_ordering());
				$above->set_ordering($tmp);

				$current->save($dbc);
				$above->save($dbc);
			}
		}
	}


	public static function move_down(DB_Connection $dbc, $vpage_id, $category_id) {
		tangra_if_not_int_throw_e($vpage_id);
		tangra_if_not_int_throw_e($category_id);

		$current = new TCMS_VPage_Category_DBC();

		if ($current->load_by_vpage_and_category($dbc, $vpage_id, $category_id)) {
			$sql = "select id from tcms_vpage_categories where "
						."category = $category_id "
						." and ordering > ".$current->get_ordering()
						." order by ordering asc";
			$rez = $dbc->execute($sql);
			if (!$rez->is_eod()) {
				$above = new TCMS_VPage_Category_DBC();
				$rez_obj = $rez->fetch_object();
				$above->load_by_id($dbc, $rez_obj->ID);

				// Swaping orders
				$tmp = $current->get_ordering();
				$current->set_ordering($above->get_ordering());
				$above->set_ordering($tmp);

				$current->save($dbc);
				$above->save($dbc);
			}
		}
	}


	public static function move_top(DB_Connection $dbc, $vpage_id, $category_id) {
		tangra_if_not_int_throw_e($vpage_id);
		tangra_if_not_int_throw_e($category_id);

		$sql = "update tcms_vpage_categories set ordering = 0 where "
					."category = $category_id "
					." and vpage = $vpage_id ";
		$rez = $dbc->execute($sql);

		TCMS_VPage_Category_DBC::recalc_ordering($dbc, $category_id);
	}


	public static function move_bottom(DB_Connection $dbc, $vpage_id, $category_id) {
		tangra_if_not_int_throw_e($vpage_id);
		tangra_if_not_int_throw_e($category_id);

		$sql = "select max(ordering) as mord from tcms_vpage_categories where category = $category_id ";
		$rez = $dbc->execute($sql);
		$rez_obj = $rez->fetch_object();
		$last = $rez_obj->MORD + 10;

		$sql = "update tcms_vpage_categories set ordering = $last where "
					."category = $category_id "
					." and vpage = $vpage_id ";
		$rez = $dbc->execute($sql);

		TCMS_VPage_Category_DBC::recalc_ordering($dbc, $category_id);
	}


	public static function get_next_ordering(DB_Connection $dbc, $category_id) {
		tangra_if_not_int_throw_e($category_id);
		$sql = "select max(ordering) as mord from tcms_vpage_categories where category = $category_id ";
		$rez = $dbc->execute($sql);
		$rez_obj = $rez->fetch_object();
		$last = $rez_obj->MORD + 10;

		return $last;
	}


	public static function find_vpage_newer_last_modified_for_cat(DB_Connection $dbc, $category_id, $datetime) {
		tangra_if_not_int_throw_e($category_id);
		if (!check_timestamp($datetime)) {
			throw new Tangra_Module_Exception('Invalid datetime: '.$datetime);
		}

		$ret = false;

		$sql = "select vp.last_modified as lm ".
						"from tcms_vpages as vp, ".
								"tcms_vpage_categories as vc ".
						"where vc.category = $category_id and ".
								"vp.id = vc.vpage and ".
								"vp.last_modified > '$datetime' order by lm desc";
		$rez = $dbc->execute($sql);
		if (!$rez->is_eod()) {
			$rez_obj = $rez->fetch_object();
			$ret = $rez_obj->LM;
		}

		return $ret;
	}
}
